package com.ruoyi.door.service;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.easy.query.api.proxy.client.EasyEntityQuery;
import com.easy.query.core.basic.jdbc.tx.Transaction;
import com.easy.query.core.util.EasyCollectionUtil;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.domain.entity.MemberUser;
import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.core.redis.RedisCache;
import com.ruoyi.system.domain.SysUserRole;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.SysDeptUtil;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.door.controller.admin.house.vo.DoorHouseImportVO;
import com.ruoyi.door.controller.admin.house.vo.DoorHouseImportFailureVO;
import com.ruoyi.door.controller.app.house.vo.AppDoorHouseStaticRespVO;
import com.ruoyi.door.converter.DateConverter;
import com.ruoyi.door.domain.DoorHouse;
import com.ruoyi.door.domain.DoorLandlord;
import com.ruoyi.door.utils.HouseUtil;
import com.ruoyi.system.mapper.SysDeptMapper;
import com.ruoyi.system.mapper.SysUserRoleMapper;
import com.ruoyi.system.service.ISysUserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Slf4j
public class DoorHouseService {
    @Autowired
    private EasyEntityQuery easyEntityQuery;
    @Autowired
    private SysDeptMapper sysDeptMapper;
    @Autowired
    private SysUserRoleMapper sysUserRoleMapper;
    @Autowired
    private ISysUserService sysUserService;
    @Autowired
    private RedisCache redisCache;
    private final String IMPORT_KRY = "houseimport";

    public AppDoorHouseStaticRespVO getDoorHouseStatic(Long memberId) {
        AppDoorHouseStaticRespVO respVO = new AppDoorHouseStaticRespVO();
        long count = easyEntityQuery.queryable(DoorHouse.class)
                .leftJoin(DoorLandlord.class, (s1, s2) -> s1.landlordId().eq(s2.id()))
                .leftJoin(MemberUser.class, (s1, s2, s3) -> s2.phone().eq(s3.phone()))
                .where((d1, d2, m3) -> m3.userId().eq(memberId))
                .count();
        respVO.setHouseCount(count);

        return respVO;
    }

    @Transactional
    public Map<String, Object> importData(MultipartFile file, String uuid) throws Exception {
        List<SysDept> deptList = sysDeptMapper.selectDeptList(new SysDept());

        int insertCount = 0;
        int updateCount = 0;
        int failCount = 0;
        List<DoorHouseImportFailureVO> failureList = new ArrayList<>();

        // 先查询数据库中的全部房产
        List<DoorHouse> allHouses = easyEntityQuery.queryable(DoorHouse.class).toList();
        
        // 创建房产唯一标识到房产对象的映射，提高查找效率
        Map<String, DoorHouse> houseKeyMap = new HashMap<>();
        for (DoorHouse house : allHouses) {
            String key = house.getCountry() + "-" + 
                        house.getProvince() + "-" + 
                        house.getCity() + "-" + 
                        house.getDistrict() + "-" + 
                        house.getStreet() + "-" + 
                        house.getVillage() + "-" + 
                        house.getAddress();
            houseKeyMap.put(key, house);
        }
        
        // 先查询数据库中的全部房东（通过用户角色关联表查询）
        List<SysUser> allLandlords = easyEntityQuery.queryable(SysUser.class)
                .toList();

        // 使用ExcelUtil直接读取Excel文件
        List<DoorHouseImportVO> houseImportVOS = new ArrayList<>();
        
        // 检查文件是否为空
        if (file.getSize() == 0) {
            // 文件为空，直接返回结果
            Map<String, Object> result = new HashMap<>();
            result.put("message", "文件为空，没有数据需要导入");
            result.put("insertCount", 0);
            result.put("updateCount", 0);
            result.put("failCount", 0);
            result.put("failureList", failureList);
            return result;
        }
        
        EasyExcel.read(file.getInputStream(), DoorHouseImportVO.class, new ReadListener<DoorHouseImportVO>() {

            @Override
            public void invoke(DoorHouseImportVO vo, AnalysisContext analysisContext) {
                houseImportVOS.add(vo);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {

            }
        }).registerConverter(new DateConverter()).autoCloseStream(true).sheet().headRowNumber(1).doRead();
        // 检查导入数据中是否有重复的房产
        //checkDuplicateHouses(houseImportVOS);
        
        // 提取并去重房东信息
        List<SysUser> importLandlords = extractLandlords(houseImportVOS);
        
        // 创建批量操作的集合
        List<SysUser> newLandlords = new ArrayList<>();
        List<DoorHouse> newHouses = new ArrayList<>();
        List<DoorHouse> updateHouses = new ArrayList<>();
        System.out.println(DateUtil.now()+"房东循环开始");
        // 创建房东电话到房东对象的映射，提高查找效率
        Map<String, SysUser> landlordPhoneMap = new HashMap<>();
        for (SysUser landlord : allLandlords) {
            landlordPhoneMap.put(landlord.getPhonenumber(), landlord);
        }

        for (SysUser importLandlord : importLandlords) {
            SysUser sysUser = landlordPhoneMap.get(importLandlord.getPhonenumber());

            if (ObjectUtil.isNull(sysUser)) {
                if(StringUtils.isEmpty(importLandlord.getNickName())){
                    importLandlord.setNickName(importLandlord.getPhonenumber());
                }
                sysUser = new SysUser();
                sysUser.setUserName(importLandlord.getPhonenumber());
                sysUser.setNickName(importLandlord.getNickName());
                sysUser.setPhonenumber(importLandlord.getPhonenumber());
                sysUser.setSex("0");
//                sysUser.setDeptId(10L));
                sysUser.setRoleIds(new Long[]{100L});
                sysUser.setPassword(SecurityUtils.encryptPassword("123456"));

                // 添加到新增房东列表中
                newLandlords.add(sysUser);

            }
        }
        System.out.println(DateUtil.now()+"房东循环结束");


        // 批量插入新房东
        System.out.println(DateUtil.now()+"房东插入开始");

        if (!newLandlords.isEmpty()) {
            // 1. 批量插入房东用户
            easyEntityQuery.insertable(newLandlords).batch().executeRows(true);
            
            // 2. 收集新插入房东的手机号
            List<String> newLandlordPhones = newLandlords.stream()
                    .map(SysUser::getPhonenumber)
                    .collect(Collectors.toList());
            
            // 3. 根据手机号查询新插入用户的 user_id
            List<SysUser> insertedUsers = easyEntityQuery.queryable(SysUser.class)
                    .where(u -> u.phonenumber().in(newLandlordPhones))
                    .toList();
            
            // 4. 构造用户角色关系数据
            List<SysUserRole> userRoles = new ArrayList<>();
            for (SysUser user : insertedUsers) {
                SysUserRole userRole = new SysUserRole();
                userRole.setUserId(user.getUserId());
                userRole.setRoleId(100L); // 房东角色ID
                userRoles.add(userRole);
            }
            
            // 5. 批量插入用户角色关系
            if (!userRoles.isEmpty()) {
                easyEntityQuery.insertable(userRoles).batch().executeRows();
            }
        }
        System.out.println(DateUtil.now()+"房东插入结束");

        allLandlords = easyEntityQuery.queryable(SysUser.class)
                .toList();

        int i = 0;
        System.out.println(DateUtil.now()+"房产循环开始");

        for (DoorHouseImportVO houseImportVO : houseImportVOS) {
            try {
                i++;
                SysDept root = new SysDept();
                root.setDeptId(0L);
                SysDept country = SysDeptUtil.findDeptById(houseImportVO.getCountryName(), 1, root, deptList);
                if (country == null) {
                    throw new Exception("公司不存在或层级关系错误");
                }
                // 验证部门信息
                SysDept province = SysDeptUtil.findDeptById(houseImportVO.getProvinceName(), 2, country, deptList);
                if (province == null) {
                    throw new Exception("省份不存在或层级关系错误");
                }

                SysDept city = SysDeptUtil.findDeptById(houseImportVO.getCityName(), 3, province, deptList);
                if (city == null) {
                    throw new Exception("城市不存在或层级关系错误");
                }

                SysDept district = SysDeptUtil.findDeptById(houseImportVO.getDistrictName(), 4, city, deptList);
                if (district == null) {
                    throw new Exception("区县不存在或层级关系错误");
                }

                SysDept street = SysDeptUtil.findDeptById(houseImportVO.getStreetName(), 5, district, deptList);
                if (street == null) {
                    throw new Exception("街道不存在或层级关系错误");
                }

                SysDept village = SysDeptUtil.findDeptById(houseImportVO.getVillageName(), 6, street, deptList);
                if (village == null) {
                    throw new Exception("村/社区不存在或层级关系错误");
                }

                // 验证房东信息
                String phone = houseImportVO.getLandlordPhone();
                
                // 验证手机号格式，如果无效直接跳过处理
                if (StringUtils.isEmpty(phone) || !isValidPhoneNumber(phone)) {
                    throw new Exception("手机号必须为11位数字");
                }
                
                SysUser sysUser = allLandlords.stream()
                        .filter(s -> s.getPhonenumber().equals(phone))
                        .findFirst().orElse(null);
                
                // 如果找不到对应的房东用户，说明手机号无效，未被创建
                if (sysUser == null) {
                    throw new Exception("房东用户不存在，手机号可能无效");
                }
                
                // 查找是否已存在相同房产
                String houseKey = country.getDeptId() + "-" + 
                                 province.getDeptId() + "-" + 
                                 city.getDeptId() + "-" + 
                                 district.getDeptId() + "-" + 
                                 street.getDeptId() + "-" + 
                                 village.getDeptId() + "-" + 
                                 houseImportVO.getAddress();
                DoorHouse existingHouse = houseKeyMap.get(houseKey);
                
                if (ObjectUtil.isNull(existingHouse)) {
                    // 创建房产信息
                    String address = houseImportVO.getAddress();
                    DoorHouse doorHouse = new DoorHouse();
                    doorHouse.setCountry(country.getDeptId());
                    doorHouse.setProvince(province.getDeptId());
                    doorHouse.setCity(city.getDeptId());
                    doorHouse.setDistrict(district.getDeptId());
                    doorHouse.setStreet(street.getDeptId());
                    doorHouse.setVillage(village.getDeptId());
                    doorHouse.setAddress(address);
                    doorHouse.setDoor(houseImportVO.getDoor());
                    doorHouse.setMonthPrice(houseImportVO.getMonthPrice());

                    doorHouse.setLandlordId(sysUser.getUserId());
                    doorHouse.setDeptId(village.getDeptId());
                    doorHouse.setBalance(houseImportVO.getBalance());
                    //开始日期
                    Date endDate = HouseUtil.calculateEndDateFromBaseDate(houseImportVO.getBalance(), houseImportVO.getMonthPrice());
                    doorHouse.setEndDate(endDate);
                    doorHouse.setStartDate(new DateTime(LocalDate.of(DateUtil.thisYear(), DateUtil.thisMonth()+1, 1)));

                    // 添加到新增房产列表中
                    newHouses.add(doorHouse);
                    // 添加到房产列表中，用于后续查询
                    allHouses.add(doorHouse);
                    insertCount++;

                } else {
                    // 更新房产信息
                    existingHouse.setDoor(houseImportVO.getDoor());
                    existingHouse.setMonthPrice(houseImportVO.getMonthPrice());
//                    existingHouse.setStartDate(houseImportVO.getStartDate());
                    Date endDate = HouseUtil.calculateEndDateFromBaseDate(houseImportVO.getBalance(), houseImportVO.getMonthPrice());
                    existingHouse.setEndDate(endDate);
                    existingHouse.setStartDate(new DateTime(LocalDate.of(DateUtil.thisYear(), DateUtil.thisMonth()+1, 1)));

                    existingHouse.setBalance(houseImportVO.getBalance());
                    existingHouse.setLandlordId(sysUser.getUserId());
                    
                    // 添加到更新房产列表中
                    updateHouses.add(existingHouse);
                    
                    updateCount++;
                }

            } catch (Exception e) {
                failCount++;

                // 记录失败的数据
                DoorHouseImportFailureVO failureVO = new DoorHouseImportFailureVO();
                failureVO.setRowNumber(i);
                failureVO.setCountryName(houseImportVO.getCountryName());
                failureVO.setProvinceName(houseImportVO.getProvinceName());
                failureVO.setCityName(houseImportVO.getCityName());
                failureVO.setDistrictName(houseImportVO.getDistrictName());
                failureVO.setStreetName(houseImportVO.getStreetName());
                failureVO.setVillageName(houseImportVO.getVillageName());
                failureVO.setAddress(houseImportVO.getAddress());
                failureVO.setLandlordName(houseImportVO.getLandlordName());
                failureVO.setLandlordPhone(houseImportVO.getLandlordPhone());
                failureVO.setDoor(houseImportVO.getDoor());
                failureVO.setMonthPrice(houseImportVO.getMonthPrice());
                failureVO.setStartDate(houseImportVO.getStartDate());
                failureVO.setEndDate(houseImportVO.getEndDate());
                failureVO.setBalance(houseImportVO.getBalance());
                failureVO.setErrorMessage(e.getMessage());
                failureList.add(failureVO);
            }
        }
        System.out.println(DateUtil.now()+"房产循环结束");
        System.out.println(DateUtil.now()+"房产c插入开始");

        if (!newHouses.isEmpty()) {
            easyEntityQuery.insertable(newHouses).batch().executeRows(true);
        }

        // 批量更新房产
        if (!updateHouses.isEmpty()) {
            easyEntityQuery.updatable(updateHouses).batch().executeRows();
        }
        System.out.println(DateUtil.now()+"房产c插入结束");

        //一次性提交的底层也是一批一批提交
        //eq的事务必须是try resources的形式
        //或者try finally close
        //eq默认也是addBatch到1000后executeBatch
        // 批量插入新房产
//        try (Transaction transaction = easyEntityQuery.beginTransaction()){
//            if (!newHouses.isEmpty()) {
//                easyEntityQuery.insertable(newHouses).batch().executeRows(true);
//            }
//
//            // 批量更新房产
//            if (!updateHouses.isEmpty()) {
//                easyEntityQuery.updatable(newHouses).batch().executeRows();
//            }
//            transaction.commit();
//        }finally {
//
//        }



        Map<String, Object> result = new HashMap<>();
        result.put("message", StrUtil.format("完成!新增{},修改{},失败{}!", insertCount, updateCount, failCount));
        result.put("insertCount", insertCount);
        result.put("updateCount", updateCount);
        result.put("failCount", failCount);
        result.put("failureList", failureList);
        
        return result;
    }
    
    /**
     * 检查导入数据中是否有重复的房产
     * 
     * @param houseImportVOS 导入的房产数据
     * @throws Exception 如果有重复房产则抛出异常
     */
    private void checkDuplicateHouses(List<DoorHouseImportVO> houseImportVOS) throws Exception {
        Set<String> houseKeys = new HashSet<>();
        for (DoorHouseImportVO vo : houseImportVOS) {
            String key = vo.getCountryName() + "-" + 
                    vo.getProvinceName() + "-" + 
                    vo.getCityName() + "-" + 
                    vo.getDistrictName() + "-" + 
                    vo.getStreetName() + "-" + 
                    vo.getVillageName() + "-" + 
                    vo.getAddress();
            
            if (!houseKeys.add(key)) {
                throw new Exception("导入数据中存在重复的房产: " + key);
            }
        }
    }

    /**
     * 提取并去重房东信息
     * @param houseImportVOS
     * @return
     */
    private List<SysUser> extractLandlords(List<DoorHouseImportVO> houseImportVOS) {
        List<SysUser> landlordMap = new ArrayList<>();

        for (DoorHouseImportVO vo : houseImportVOS) {
            String phone = vo.getLandlordPhone();
            // 只有手机号有效（严格11位数字）才提取房东信息
            if (StrUtil.isNotEmpty(phone) && isValidPhoneNumber(phone) && 
                landlordMap.stream().noneMatch(s -> s.getPhonenumber().equals(phone))) {
                SysUser user = new SysUser();
                user.setUserName(phone);
                user.setNickName(vo.getLandlordName());
                user.setPhonenumber(phone.trim());
                landlordMap.add( user);
            }
        }

        return landlordMap;
    }
    
    /**
     * 验证手机号格式：必须为严格的11位数字
     * 
     * @param phone 手机号
     * @return 验证结果
     */
    private boolean isValidPhoneNumber(String phone) {
        if (phone == null || phone.trim().isEmpty()) {
            return false;
        }
        
        // 移除所有空格
        phone = phone.trim();
        
        // 检查长度是否为11位
        if (phone.length() != 11) {
            return false;
        }
        
        // 检查是否全为数字
        return phone.matches("\\d{11}");
    }

    /**
     * 更新房产到期时间
     * 根据房产的 startDate 和当前月份计算月份差，扣减相应的费用，并更新到期时间
     * 
     * @param doorHouse 房产对象
     * @return 更新是否成功
     */
    public boolean updateHouseEndDate(DoorHouse doorHouse) {
        try {
            // 获取当前月份1号
            Date firstDayOfMonth = DateUtil.beginOfMonth(new Date());
            
            // 获取房产信息
            BigDecimal currentBalance = doorHouse.getBalance();
            BigDecimal monthPrice = doorHouse.getMonthPrice();
            Date startDate = doorHouse.getStartDate();
            
            // 验证必要字段
            if (ObjectUtil.isNull(currentBalance) || ObjectUtil.isNull(monthPrice)) {
                log.warn("房产ID: {} 余额或月租为空", doorHouse.getId());
                return false;
            }
            
            if (ObjectUtil.isNull(startDate)) {
                log.warn("房产ID: {} 开始日期为空", doorHouse.getId());
                return false;
            }
            
            if (monthPrice.compareTo(BigDecimal.ZERO) <= 0) {
                log.warn("房产ID: {} 月租必须大于0", doorHouse.getId());
                return false;
            }
            
            // 判断 startDate 是否已经是当前月份1号或之后，如果是说明本月已处理过
            Date startMonthBegin = DateUtil.beginOfMonth(startDate);
            // 使用 after 或 equals 判断
            if (startMonthBegin.after(firstDayOfMonth) || startMonthBegin.equals(firstDayOfMonth)) {
                log.warn("房产ID: {} 本月已处理过，startDate: {}，当前月份1号: {}", 
                        doorHouse.getId(), 
                        DateUtil.format(startDate, "yyyy-MM-dd"),
                        DateUtil.format(firstDayOfMonth, "yyyy-MM-dd"));
                return false;
            }
            
            // 计算 startDate 和当前月份1号之间相差多少个月
            long monthsDiff = DateUtil.betweenMonth(startMonthBegin, firstDayOfMonth, false);
            
            if (monthsDiff <= 0) {
                log.warn("房产ID: {} 开始日期必须小于当前月份，月份差: {}", doorHouse.getId(), monthsDiff);
                return false;
            }
            
            // 余额减去（月份差 × 月租），允许负数表示欠费
            BigDecimal totalDeduction = monthPrice.multiply(BigDecimal.valueOf(monthsDiff));
            BigDecimal newBalance = currentBalance.subtract(totalDeduction);
            
            // 使用 HouseUtil 计算新的到期时间
            Date newEndDate = HouseUtil.calculateEndDateFromBaseDate(newBalance, monthPrice);
            
            // 更新房产的余额和到期时间
            long updateRows = easyEntityQuery.updatable(DoorHouse.class)
                    .setColumns(h -> {
                        h.balance().set(newBalance);
                        h.endDate().set(newEndDate);
                        h.startDate().set(firstDayOfMonth);
                    })
                    .where(h -> h.id().eq(doorHouse.getId()))
                    .executeRows();
            
            if (updateRows > 0) {
                log.info("房产ID: {} 更新成功，月份差: {}个月，扣费: {}，原余额: {}，新余额: {}{}，新到期时间: {}", 
                        doorHouse.getId(), monthsDiff, totalDeduction, currentBalance, newBalance, 
                        newBalance.compareTo(BigDecimal.ZERO) < 0 ? "（欠费）" : "", newEndDate);
                return true;
            } else {
                log.warn("房产ID: {} 更新失败，房产不存在", doorHouse.getId());
                return false;
            }
            
        } catch (Exception e) {
            log.error("房产ID: {} 更新失败", doorHouse.getId(), e);
            return false;
        }
    }
}
